const express = require("express");

const router = express.Router();

const dayjs = require("dayjs")

const db = require("../db/mysql")
const path = require("path");
const {filePath} = require("../config");
const xlsx = require("xlsx");

router.post("/createTask", (req, res) => {
    let sql = "INSERT INTO tasks SET ?"
    req.body = {
        ...req.body,
        createDate: dayjs().format("YYYY-MM-DD HH:mm:ss"),
        updateDate: dayjs().format("YYYY-MM-DD HH:mm:ss"),
    }
    db.query(sql, req.body, (err, results) => {
        if (err) {
            return res.send({
                code: 400, message: err.message,
            })
        }
        if (results.affectedRows !== 1) {
            return res.send({
                code: 500, message: "任务添加失败",
            })
        }
        res.send({
            code: 200, message: "任务添加成功",
        })
    })

})

router.post("/updateTask", (req, res) => {
    let sql = "UPDATE tasks SET ? WHERE id = ?"
    req.body = {
        ...req.body,
        createDate: req.body.createDate ? req.body.createDate : dayjs().format("YYYY-MM-DD HH:mm:ss"),
        updateDate: dayjs().format("YYYY-MM-DD HH:mm:ss"),
    }
    db.query(sql, [req.body, req.body.id], (err, results) => {
        if (err) {
            return res.send({
                code: 400, message: err.message,
            })
        }
        if (results.affectedRows !== 1) {
            return res.send({
                code: 500, message: "任务更新失败",
            })
        }
        res.send({
            code: 200, message: "任务更新成功", data: req.body
        })
    })

})

router.post("/deleteTask", (req, res) => {

    let sql = "UPDATE tasks SET is_delete = ?,updateDate = ?  WHERE id = ?"
    db.query(sql, [1, dayjs().format("YYYY-MM-DD HH:mm:ss"), Number(req.body.id)], (err, results) => {
        if (err) {
            return res.send({
                code: 400, message: err.message,
            })
        }
        if (results.affectedRows !== 1) {
            return res.send({
                code: 500, message: "任务删除失败",
            })
        }
        res.send({
            code: 200, message: "任务删除成功", data: req.body
        })
    })

})


router.get("/list", (req, res) => {
    let sql = "SELECT * FROM tasks WHERE is_delete = 0"
    db.query(sql, (err, results) => {
        if (err) {
            return res.send({
                code: 500, message: err.message
            })
        }
        res.send({
            code: 200, message: "数据获取成功", data: results
        })
    })
})

//获取材料清单
router.get("/billMaterials", (req, res) => {

    const filePaths = path.join(__dirname, '../', filePath, '0b208406-65bc-4295-9410-ab917eceadd1.xlsx');
    const workbook = xlsx.readFile(filePaths, {cellStyles: true});
    const sheet_name = workbook.SheetNames[0];
    const worksheet = workbook.Sheets[sheet_name];
    const htmlTable = xlsx.utils.sheet_to_html(worksheet, {editable: true});
    const editable_html = htmlTable.match(/<table[^>]*>([\s\S]*?)<\/table>/i);
    res.send({
        code: 200, message: "材料清单获取成功", data: editable_html && editable_html[0],
    })

})
//导出材料表

router.get("/billExport", (req, res) => {
    let sql = "SELECT * FROM tasks WHERE id = ?";
    db.query(sql, req.query.id, (err, results) => {
        if (err) {
            return res.send({
                code: 500, message: err.message
            })
        }
        var workbook = xlsx.read(results[0].billHtml, {type: "string"});
        const excelBuffer = xlsx.write(workbook, {type: 'buffer', bookType: 'xlsx'});
        xlsx.writeFile(workbook, results[0].userName + "材料清单.xlsx");
        // 设置返回的http header信息，告诉浏览器该文件是一个excel文件
        res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');

        res.setHeader("Access-Control-Expose-Headers", "Content-Disposition");
        res.setHeader('Content-Disposition', 'attachment; filename=' + encodeURIComponent(results[0].userName + "材料清单.xlsx"));

        res.send(excelBuffer);
    })


})


module.exports = router;
